
from datetime import datetime
import sys,random
import pyodbc,traceback,time
class sqlseverDB:
    
    '''
    操作SQL Server的类
    '''

    def __init__(self, DRIVER, SERVER, DATABASE, UID, PWD, charset='utf-8'):
        
        '''
        功能：初始化配置信息
        DRIVER：  数据驱动器
        SERVER：  数据服务器
        DATABASE：数据库
        UID：     登录名
        PWD：     登录密码
        charset:  字符编码
        '''
        #self.DRIVER = DRIVER
        self.DRIVER='{ODBC Driver 17 for SQL Server}'
        self.SERVER = SERVER
        self.DATABASE = DATABASE
        self.UID = UID
        self.PWD = PWD
        self.chartset = charset
    
    def __getConnect(self):
        '''
        功能：利用配置信息连接SQL Server
        '''

        if self.SERVER=='.':
            conn_str = f'DRIVER={self.DRIVER};SERVER={self.SERVER};Trusted_Connection=yes;DATABASE={self.DATABASE}'
        else:
            conn_str=f'DRIVER={self.DRIVER};SERVER={self.SERVER};UID={self.UID};PWD={self.PWD};DATABASE={self.DATABASE}'
        try:
            conn = pyodbc.connect(conn_str)
            
        except Exception as ex:
            print('SQL Server connecting error,reason is:'+str(ex))
            sys.exit()
        
        return conn
    
    def __getConnect2(self,DBName='master'):
        if self.SERVER=='.':
            conn_str = f'DRIVER={self.DRIVER};SERVER={self.SERVER};Trusted_Connection=yes;DATABASE={DBName}'
        else:
            conn_str=f'DRIVER={self.DRIVER};SERVER={self.SERVER};UID={self.UID};PWD={self.PWD};DATABASE={DBName}'

        try:
            conn = pyodbc.connect(conn_str)
        except Exception as ex:
            print('SQL Server connecting error,reason is:'+str(ex))
            return
        return conn
    
    def getALLDataBase(self):
        err_count = 0
        system_dbs = {'master', 'tempdb', 'model', 'msdb','ReportServer', 'ReportServerTempDB'}

        while True:
            try:
                conn = self.__getConnect2()
                cur = conn.cursor()

                # 查询所有在线数据库
                cur.execute("SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'")
                all_dbs = cur.fetchall()

                # 过滤非系统数据库
                user_dbs = [db.name for db in all_dbs if db.name not in system_dbs]
                break

            except pyodbc.Error as ex:
                print(f'数据库查询错误 => {ex}, 正在重试...')
                time.sleep(3)
                err_count += 1

                if err_count >= 10:
                    print('错误超过10次，退出任务系统')
                    raise

            finally:
                try:
                    cur.close()
                    conn.close()
                except:
                    pass

        return user_dbs

    def create_table(self, table_name, columns, primary_key=None, if_not_exists=True):
        """
        功能：创建一个新表
        参数：
            table_name: 表名 (str)
            columns: 字段定义 (dict)，例如：
                     {
                         "id": "INT IDENTITY(1,1) NOT NULL",
                         "name": "NVARCHAR(100)",
                         "price": "DECIMAL(10,2)",
                         "create_time": "DATETIME DEFAULT GETDATE()"
                     }
            primary_key: 主键列名 (str 或 list)，例如 "id" 或 ["id", "name"]
            if_not_exists: 若表存在则跳过创建 (bool)
        返回：
            1 创建成功
            0 创建失败
        """

        # 拼接列定义
        col_defs = ", ".join([f"[{col}] {dtype}" for col, dtype in columns.items()])

        # 拼接主键
        pk_str = ""
        if primary_key:
            if isinstance(primary_key, (list, tuple)):
                pk_str = f", PRIMARY KEY ({', '.join(['['+x+']' for x in primary_key])})"
            else:
                pk_str = f", PRIMARY KEY ([{primary_key}])"

        # 生成完整 SQL
        if if_not_exists:
            sql = f"""
            IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='{table_name}' AND xtype='U')
            CREATE TABLE [{table_name}] ({col_defs}{pk_str});
            """
        else:
            sql = f"CREATE TABLE [{table_name}] ({col_defs}{pk_str});"

        # 执行 SQL
        try:
            conn = self.__getConnect()
            cur = conn.cursor()
            cur.execute(sql)
            conn.commit()
            print(f"✅ 表 [{table_name}] 创建成功")
            return 1
        except pyodbc.Error as ex:
            print(f"❌ 创建表失败: {ex}\nSQL => {sql}")
            return 0
        finally:
            try:
                cur.close()
                conn.close()
            except:
                pass

    def ExecQuery(self, sql,params=None):

        '''
        功能：执行查询语句，返回结果集
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()
        err_count=0
        while True:
            try:
                
                if params:
                    cur.execute(sql,params)
                else:   
                    cur.execute(sql)
                rows = cur.fetchall()
                break

            except pyodbc.Error as ex:
                print(f'数据库查询错误 => {ex},正在重试...{sql}' )
                time.sleep(3)
                err_count+=1
            if err_count>=10:
                print(f'错误超过10次退出任务系统' )
                sys.exit()
        cur.close()
        conn.close()
        return rows

    def ExecQuerydict(self, sql,params=None):

        '''
        功能：执行查询语句，返回字典结果集
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()
        while True:
            try:
                if params:
                    cur.execute(sql,params)
                else:
                    cur.execute(sql)
                rows = cur.fetchall()
                break
            except pyodbc.Error as ex:
                print(f'数据库查询错误 => {ex},正在重试...{sql}' )
                
                time.sleep(3)
        description=cur.description
        cur.close()
        conn.close()
        rows2 = [dict(zip([column[0] for column in description], row)) for row in rows]
        return rows2
    
    def GetCol(self,tb_name):

        '''
        功能：执行查询语句，返回字典结果集
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()
        try:
            cur.execute(f'select top 1 * from {tb_name}')
            rows = cur.fetchall()

        except pyodbc.Error as ex:
            print('SQL Server.Error:%s' % str(ex))
            sys.exit()
        description=cur.description
        return [column[0] for column in description]
    
    def ExecNoQuery(self, sql,params=None):
        '''
        功能：执行查询语句，如Create,Insert,Delete,update,drop等。
        sql：要执行的SQL语句
        '''
        conn=self.__getConnect()
        cur = conn.cursor()

        try:
            if params:
                
                cur.execute(sql,params)
            else:
                cur.execute(sql)
            conn.commit()
            status=cur.rowcount
            
        except pyodbc.Error as ex:
            #traceback.print_exc()
            print(f'提交错误 => {ex},{sql},{params}')
            status=0
        cur.close()
        conn.close()
        return status

        """
        执行 SQL 语句（包括 `UPDATE ... OUTPUT`），并返回输出的结果
        """
        conn = self.__getConnect()
        cur = conn.cursor()
        output_data = []
        while True:
            try:
                if params:
                    cur.execute(sql, params)
                else:
                    cur.execute(sql)

                # 获取 `OUTPUT` 返回的行
                output_data = cur.fetchall()
                # 提交事务
                conn.commit()
                break
            except pyodbc.Error as ex:
                wait_time=random.randint(1,8)
                print(f'数据库取数据并发错误: {ex},等待 {wait_time} 秒重式...')
                time.sleep(wait_time)
                

        description=cur.description
        cur.close()
        conn.close()
        if is_dict and output_data:
            output_data = [dict(zip([column[0] for column in description], row)) for row in output_data]
        return output_data  # 返回 OUTPUT 结果

    def merge_into(self,tab_name,zds,params,zds_on,is_log_time=False):
        
        zd_on_str=' and '.join([f'target.{zd} = source.{zd}' for zd in zds_on])

        if not is_log_time:
            wstr=','.join([f'? as {zd}' for zd in zds])
            update_str=','.join([f'{zd} = source.{zd}' for zd in zds])
            insert_str=','.join(zds)
            values_str=','.join([f'source.{zd}' for zd in zds])
        else:
            wstr=','.join([f'? as {zd}' for zd in zds+['update_time','upload_time']])
            cur_time=datetime.now()
            update_str=','.join([f'{zd} = source.{zd}' for zd in zds+['update_time']])
            insert_str=','.join(zds+['update_time','upload_time'])
            values_str=','.join([f'source.{zd}' for zd in zds+['update_time','upload_time']])
            params=params+[cur_time,cur_time]


        sqlstr=f'''MERGE INTO {tab_name} AS target
        USING (SELECT {wstr}) AS source
        ON {zd_on_str}
        WHEN MATCHED THEN
            UPDATE SET {update_str}
        WHEN NOT MATCHED THEN
            INSERT ({insert_str})
            VALUES ({values_str});'''
        return self.ExecNoQuery(sqlstr,params)

    def call_procedure(self,proce_name):

        '''调用存储过程'''

        # 设置数据库连接参数

        try:
            # 连接数据库
            conn=self.__getConnect()
            cur = conn.cursor()

            # 执行存储过程
            cur.execute(f"{{CALL {proce_name}}}")

            # 提交事务
            conn.commit()
            status=cur.rowcount

        except pyodbc.Error as e:
            print(f"存储过程执行失败: {e}")
            status=0
        finally:
            # 关闭连接
            conn.close()
        
        return status

    def call_procedure_rows(self,proce_name,*args):

        '''调用存储过程'''

        # 设置数据库连接参数
        rows=[]
        try:
            # 连接数据库
            conn=self.__getConnect()
            cur = conn.cursor()

            # 执行存储过程
          
            if len(args)==0:
                cur.execute(f"{{CALL {proce_name}}}")
            else:
                wstr=','.join(['?' for _ in args])
                cur.execute(f"{{CALL {proce_name}({wstr})}}",args)
            rows = cur.fetchall()

        except pyodbc.Error as e:
            print(f"存储过程执行失败: {e}")
        finally:
            # 关闭连接
            conn.close()
        
        return rows


    def get_col_des_from_procedure(self,table_name):

        rs=self.call_procedure_rows('sp_GetTableColumnComments',table_name)
        col_en_to_cn={}
        for tb,en_name,cn_name in rs:

            col_en_to_cn[en_name]=cn_name
        
        return col_en_to_cn


    def add_column_comment(self,table, column, comment):
        """
        为 SQL Server 某个字段添加（或更新）注释 MS_Description
        """

        sql = """
        DECLARE @ret INT;

        -- 如果已存在则更新，否则添加
        IF EXISTS (
            SELECT 1 FROM sys.extended_properties 
            WHERE major_id = OBJECT_ID(?) 
            AND minor_id = (
                SELECT column_id FROM sys.columns 
                WHERE object_id = OBJECT_ID(?) AND name = ?
            )
            AND name = 'MS_Description'
        )
        BEGIN
            EXEC @ret = sys.sp_updateextendedproperty
                @name = N'MS_Description',
                @value = ?,
                @level0type = N'SCHEMA', @level0name = N'dbo',
                @level1type = N'TABLE',  @level1name = ?,
                @level2type = N'COLUMN', @level2name = ?;
        END
        ELSE
        BEGIN
            EXEC @ret = sys.sp_addextendedproperty
                @name = N'MS_Description',
                @value = ?,
                @level0type = N'SCHEMA', @level0name = N'dbo',
                @level1type = N'TABLE',  @level1name = ?,
                @level2type = N'COLUMN', @level2name = ?;
        END

        SELECT @ret AS Result;
        """

        try:
            conn=self.__getConnect()
            cur = conn.cursor()

            params = (
                table,          # OBJECT_ID(?)
                table,          # OBJECT_ID(?)
                column,         # 查询列
                comment,        # update 传 comment
                table, column,  # update 传表/列
                comment,        # add 传 comment
                table, column   # add 传表/列
            )

            cur.execute(sql, params)
            conn.commit()

            print(f"字段 {table}.{column} 注释已更新为：{comment}")

        except Exception as e:
            print("添加注释失败: ", e)

        finally:
            conn.close()